cd "C:\Users\pgi1\Dropbox\Coal 2021 _ Joe Peter\Bank Regressions\data"



//////////////////////////////////////////////////////////////
//   First, collapse the data I want to use to lender-year level
use lenders_borrowers_dataset, clear

keep if !mi( prod_tons )
collapse (sum) deal_amount_mtr=deal_amount_converted (count) loans_mtr=deal_amount_converted (mean) maturity revolver secured_loan allindrawn_max, by(ultimateparentid year)

bysort ultimateparentid year: keep if _n==1
save mtr_loans_by_year, replace


///////////////////////////////////////////////////////////
// Next, split loans by relationship

use lenders_borrowers_dataset, clear
keep if !mi(prod_tons)
keep if deals5>=1 

collapse (count) loans_mtr_rel=deal_amount_converted , by(ultimateparentid year)

bysort ultimateparentid year: keep if _n==1
save mtr_loans_rel_by_year, replace


use lenders_borrowers_dataset, clear
keep if !mi(prod_tons)
keep if deals5==0

collapse (count) loans_mtr_norel=deal_amount_converted , by(ultimateparentid year)

bysort ultimateparentid year: keep if _n==1
save mtr_loans_norel_by_year, replace


//////////////////////////////////////////////
// Do total loans


use lenders_borrowers_dataset, clear

collapse (sum) deal_amount_total=deal_amount_converted (count) loans_total=deal_amount_converted, by(ultimateparentid year)
drop if year==2021

tsset ultimateparentid year


bysort ultimateparentid year: keep if _n==1
save total_loans_by_year, replace


use lenders_borrowers_dataset, clear

rename primarysiccode sic
keep if ((sic>= 1220 & sic<=1221) | (sic>=3310 & sic<=3329) | (sic>=4900 & sic<=4939))

collapse (sum) deal_amount_related=deal_amount_converted (count) loans_related=deal_amount_converted, by(ultimateparentid year)
drop if year==2021

bysort ultimateparentid year: keep if _n==1
save related_loans_by_year, replace




/////////////////////////////////////////////////////////////
// Next, make a square dataset
use ultimateparentid  enhancedreview projectban companyban minpolicyyear lender lender_country using lenders_borrowers_dataset, clear
bysort ultimateparentid: keep if _n==1

destring enhancedreview projectban companyban minpolicyyea, force replace

expand 26
bysort ultimateparentid: gen year=1995+_n-1
tab year

// add total mtr loans
bysort ultimateparentid year: keep if _n==1
joinby ultimateparentid year using mtr_loans_by_year, unm(both)
tab _m
keep if _m==1 | _m==3
drop _m
rm mtr_loans_by_year.dta

// add rel mtr loans
bysort ultimateparentid year: keep if _n==1
joinby ultimateparentid year using mtr_loans_rel_by_year, unm(both)
tab _m
keep if _m==1 | _m==3
drop _m
rm mtr_loans_rel_by_year.dta

// add norel mtr loans
bysort ultimateparentid year: keep if _n==1
joinby ultimateparentid year using mtr_loans_norel_by_year, unm(both)
tab _m
keep if _m==1 | _m==3
drop _m
rm mtr_loans_norel_by_year.dta



// add related coal loans
bysort ultimateparentid year: keep if _n==1
joinby ultimateparentid year using related_loans_by_year, unm(both)
tab _m
keep if _m==1 | _m==3
drop _m
rm related_loans_by_year.dta

// add total loans
bysort ultimateparentid year: keep if _n==1
joinby ultimateparentid year using total_loans_by_year, unm(both)
tab _m
keep if _m==1 | _m==3
drop _m
rm total_loans_by_year.dta

foreach var of varlist deal_amount_mtr loans_mtr loans_mtr_rel loans_mtr_norel deal_amount_total loans_total deal_amount_related loans_related {
qui	replace `var'=0 if mi(`var')
}

sum deal_amount_mtr loans_mtr deal_amount_total loans_total deal_amount_related loans_related, de

compress
bysort ultimateparentid year: keep if _n==1
save loan_year_dataset, replace

exit
